﻿#include "SQLiteImplement.h"
#include <QtSql/QSqlError>
#include <QVariant>
#include <QSqlQuery>
#include <QDateTime>
#include <QDebug>

// 在加载 Recipe 时打开数据库。之后就不关闭数据库了。
// 因此保存数据库时可以直接往数据库里插入信息。
SQLiteImplement::SQLiteImplement()
{
    m_database = QSqlDatabase::addDatabase("QSQLITE");
}

int SQLiteImplement::saveData(MeasurementData *data, QString label, QDateTime t)
{
    Q_UNUSED(label);
    if(!m_database.isOpen())
    {
        return -1;
    }
    QString timeString = t.toString("yyyy-MM-dd hh:mm:ss"); // 时间一律采用 ISO 8601 格式
    QString panelID = data->panelID();

    QString sqlCommand = QString("INSERT INTO MeasureData (PanelID, Time");
    int N = data->count();
    for(int i = 0; i < N; i++)
    {
        sqlCommand += QString(", Measure%1").arg(i + 1, 2, 10, QChar('0'));
    }
    sqlCommand += QString(") VALUES ('%1', '%2'").arg(panelID).arg(timeString);
    for(int i = 0; i < N; i++)
    {
        data->moveTo(i);
        MeasurePair &pair = data->currentMeasure();
        sqlCommand += QString(", '%1'").arg(pair.value_mm);
    }
    sqlCommand += QString(")");

    QSqlQuery query;
    if(query.exec(sqlCommand))
    {
        m_database.close();
        return 1;
    }
    return -2;
}

int SQLiteImplement::saveData(MeasurementData *data, QString label)
{
    return saveData(data, label, QDateTime::currentDateTime());
}

int SQLiteImplement::readSpec(MeasurementData *data, int N)
{
    QSqlQuery query;
    query.prepare("SELECT * FROM MeasureSpec");
    if(!query.exec() ) return -2;
    data->clear();
    while(query.next())
    {
        MeasurePair pair;
        pair.specID = query.value("measureID").toInt();
//        pair.name = query.value("name").toString();
        pair.CL =query.value("CL").toDouble();
        pair.UCL = query.value("UCL").toDouble();
        pair.LCL = query.value("LCL").toDouble();
        pair.USL = query.value("USL").toDouble();
        pair.LSL = query.value("LSL").toDouble();
        pair.side = query.value("side").toInt();
        pair.start.x_mm = query.value("STARTx").toDouble();
        pair.start.y_mm = query.value("STARTy").toDouble();
        pair.start.z_mm = query.value("STARTz").toDouble();
        pair.start.scale = query.value("STARTSCALE").toDouble();
        pair.start.lens = query.value("SRARTLENS").toDouble();
        pair.start.exposure = query.value("STARTEXPOURETIME").toDouble();
        pair.start.topLampIntensity = query.value("STARTINTENSITY_TOP").toInt();
        pair.start.bottomLampIntensity = query.value("STARTINTENSITY_BOTTOM").toInt();
        pair.end.x_mm = query.value("ENDx").toDouble();
        pair.end.y_mm = query.value("ENDy").toDouble();
        pair.end.z_mm = query.value("ENDz").toDouble();
        pair.end.scale = query.value("ENDSCALE").toDouble();
        pair.end.lens = query.value("ENDLENS").toDouble();
        pair.end.exposure = query.value("ENDEXPOURETIME").toDouble();
        pair.end.topLampIntensity = query.value("ENDINTENSITY_TOP").toInt();
//        pair.end.bottomLampIntensity = query.value("ENDINTENSITY_BOTTOM").toInt();
//        pair.start.dir = static_cast<MEASURE_DIR>(query.value("START_DIR").toInt());
//        pair.start.pattern = static_cast<MEASURE_PATTERN>(query.value("START_PATTERN").toInt());
//        pair.end.dir = static_cast<MEASURE_DIR>(query.value("END_DIR").toInt());
//        pair.end.pattern = static_cast<MEASURE_PATTERN>(query.value("END_PATTERN").toInt());
        data->append(pair);
    }
    if(data->count() != N) return -3;
    return 1;
}

int SQLiteImplement::loadRecipe(MeasurementData *data, QString label)
{
    if(m_database.isOpen()) //关掉旧的数据库
    {
        m_database.close();
    }
    m_database.setDatabaseName(label);
    if (!m_database.open())
    {
        return -1;
    }

    QSqlQuery query;
    query.prepare("SELECT * FROM PanelInfo;");
    query.exec();
    if(!query.next()) return -2;
    QString recipeName = query.value("PanelName").toString();
    QString description = query.value("Description").toString();
    int N = query.value("Pairs").toInt();
    data->setRecipeName(recipeName, 0, description); //这个数据库没有 RecipeID，所以填 0
    int ret = readSpec(data, N);
//    m_database.close(); // 加载数据库后不关闭
    return ret;
}

int SQLiteImplement::createRecipe(MeasurementData *data, QString label)
{
    if(m_database.isOpen())
    {
        m_database.close();
    }
    m_database.setDatabaseName(label);
    if (!m_database.open())
    {
        qWarning() << u8"无法打开数据库文件";
        return -1;
    }
    int ret = createTable(data);
    if ( ret != 1)
    {
        qWarning() << u8"createTable() 失败";
        m_database.close();
        return ret;
    }
    ret = writeSpec(data);
    m_database.close();
    return ret;
}

int SQLiteImplement::createTable(MeasurementData *data)
{
    QSqlQuery query;
    QString sqlcommand = QString("CREATE TABLE 'PanelInfo' ("
                                 "'PanelName' TEXT NOT NULL UNIQUE,"
                                 "'Description' TEXT,"
                                 "'Pairs' INTEGER NOT NULL"
                                 ")");
    if(!query.exec(sqlcommand))
    {
        return -2;
    }

    QString recipeName = data->recipeName();
    QString description = data->description();
    int N = data->count();
    sqlcommand = QString("INSERT INTO PanelInfo (PanelName, Description, Pairs)"
                         " VALUES ('%1', '%2', %3)")
                              .arg(recipeName)
                              .arg(description)
                              .arg(N);
    if(!query.exec(sqlcommand))
    {
        return -3;
    }

    sqlcommand = QString("CREATE TABLE 'MeasureSpec' ("
                         "'MeasureID' INTEGER NOT NULL UNIQUE,"
                         "'SIDE' INTEGER NOT NULL DEFAULT 0,"
                         "'CL' REAL NOT NULL DEFAULT 0,"
                         "'UCL' REAL NOT NULL DEFAULT 0,"
                         "'LCL' REAL NOT NULL DEFAULT 0,"
                         "'USL' REAL NOT NULL DEFAULT 0,"
                         "'LSL' REAL NOT NULL DEFAULT 0,"
                         "'STARTx' REAL NOT NULL DEFAULT 0,"
                         "'STARTy' REAL NOT NULL DEFAULT 0,"
                         "'STARTz' REAL NOT NULL DEFAULT 0,"
                         "'STARTSCALE' REAL NOT NULL DEFAULT 0,"
                         "'STARTLENS' INTEGER NOT NULL DEFAULT 0,"
                         "'STARTINTENSITY_TOP' INTEGER NOT NULL DEFAULT 0,"
                         "'STARTINTENSITY_BOTTOM' INTEGER NOT NULL DEFAULT 0,"
                         "'STARTEXPOURETIME' INTEGER NOT NULL DEFAULT 0,"
                         "'ENDx' REAL NOT NULL DEFAULT 0,"
                         "'ENDy' REAL NOT NULL DEFAULT 0,"
                         "'ENDz' REAL NOT NULL DEFAULT 0,"
                         "'ENDSCALE' REAL NOT NULL DEFAULT 0,"
                         "'ENDLENS' INTEGER NOT NULL DEFAULT 0,"
                         "'ENDINTENSITY_TOP' INTEGER NOT NULL DEFAULT 0,"
                         "'ENDINTENSITY_BOTTOM' INTEGER NOT NULL DEFAULT 0,"
                         "'ENDEXPOURETIME' INTEGER NOT NULL DEFAULT 0,"
                         "'START_DIR' INTEGER NOT NULL DEFAULT 0,"
                         "'START_PATTERN' INTEGER NOT NULL DEFAULT 0,"
                         "'END_DIR' INTEGER NOT NULL DEFAULT 0,"
                         "'END_PATTERN' INTEGER NOT NULL DEFAULT 0,"
                         "'STOP' BOOL NOT NULL DEFAULT FALSE,"
                         "'MESSAGE' TEXT NULL"
                         ")");

    if(!query.exec(sqlcommand))
    {
        return -4;
    }

    sqlcommand = QString("CREATE TABLE 'MeasureData' ("
                         "'PanelID' INTEGER,"
                         "'Time' DATETIME NOT NULL");

    for(int i = 1; i <= N; i++)
    {
        sqlcommand += QString(", 'Measure%1' REAL NOT NULL DEFAULT 0").arg(i, 2, 10, QChar('0'));
    }
    sqlcommand += QString(")");
    if(!query.exec(sqlcommand))
    {
        return -5;
    }
    return 1;
}

int SQLiteImplement::writeSpec(MeasurementData *data)
{
    int N = data->count();
    if(N == 0)
    {
        return -6;
    }
    QSqlQuery query;
    for(int i = 0; i < N; i++)
    {
        query.prepare("INSERT INTO "
                  "MeasureSpec (MeasureID, SIDE, CL, UCL, LCL, USL, LSL, "
                               "STARTx, STARTy, STARTz, STARTSCALE, STARTLENS, STARTINTENSITY_TOP, STARTINTENSITY_BOTTOM, STARTEXPOURETIME, "
                               "ENDx, ENDy, ENDz, ENDSCALE, ENDLENS, ENDINTENSITY_TOP, ENDINTENSITY_BOTTOM, ENDEXPOURETIME, "
                               "START_DIR, START_PATTERN, END_DIR, END_PATTERN)"
                   "VALUES (:MeasureID, :SIDE, :CL, :UCL, :LCL, :USL, :LSL, "
                           ":STARTx, :STARTy, :STARTz, :STARTSCALE, :STARTLENS, :STARTINTENSITY_TOP, :STARTINTENSITY_BOTTOM, :STARTEXPOURETIME,"
                           ":ENDx, :ENDy, :ENDz, :ENDSCALE, :ENDLENS, :ENDINTENSITY_TOP, :ENDINTENSITY_BOTTOM, :ENDEXPOURETIME, "
                           ":START_DIR, :START_PATTERN, :END_DIR, :END_PATTERN);");
        data->moveTo(i);
        MeasurePair &pair = data->currentMeasure();
        query.bindValue(":MeasureID", i);
        query.bindValue(":SIDE", pair.side);
        query.bindValue(":CL", pair.CL);
        query.bindValue(":UCL", pair.UCL);
        query.bindValue(":LCL", pair.LCL);
        query.bindValue(":USL", pair.USL);
        query.bindValue(":LSL", pair.LSL);
        query.bindValue(":STARTx", pair.start.x_mm);
        query.bindValue(":STARTy", pair.start.y_mm);
        query.bindValue(":STARTz", pair.start.z_mm);
        query.bindValue(":STARTSCALE", pair.start.scale);
        query.bindValue(":STARTLENS", pair.start.lens);
        query.bindValue(":STARTINTENSITY_TOP", pair.start.topLampIntensity);
        query.bindValue(":STARTINTENSITY_BOTTOM", pair.start.bottomLampIntensity);
        query.bindValue(":STARTEXPOURETIME", pair.start.exposure);
        query.bindValue(":ENDx", pair.end.x_mm);
        query.bindValue(":ENDy", pair.end.y_mm);
        query.bindValue(":ENDz", pair.end.z_mm);
        query.bindValue(":ENDSCALE", pair.end.scale);
        query.bindValue(":ENDLENS", pair.end.lens);
        query.bindValue(":ENDINTENSITY_TOP", pair.end.topLampIntensity);
        query.bindValue(":ENDINTENSITY_BOTTOM", pair.end.bottomLampIntensity);
        query.bindValue(":ENDEXPOURETIME", pair.end.exposure);
        query.bindValue(":START_DIR", pair.start.dir);
        query.bindValue(":START_PATTERN", pair.start.pattern);
        query.bindValue(":END_DIR", pair.end.dir);
        query.bindValue(":END_PATTERN", pair.end.pattern);

        if(!query.exec())
        {
            qDebug() << "N = " << N;
            return -7;
        }
    }
    return 1;
}

//int SQLiteImplement::writeSpec(MeasurementData *data)
//{
//    int N = data->count();
//    if(N == 0)
//    {
//        return -6;
//    }
//    QSqlQuery query;
//    for(int i = 0; i < N; i++)
//    {
//        QString cmd = "INSERT INTO "
//                      "MeasureSpec (MeasureID, SIDE, CL, UCL, LCL, USL, LSL, "
//                                   "STARTx, STARTy, STARTz, STARTSCALE, STARTLENS, STARTINTENSITY_TOP, STARTINTENSITY_BOTTOM, STARTEXPOURETIME, "
//                                   "ENDx, ENDy, ENDz, ENDSCALE, ENDLENS, ENDINTENSITY_TOP, ENDINTENSITY_BOTTOM, ENDEXPOURETIME)"
//                       " VALUES (:MeasureID, :SIDE, :CL, :UCL, :LCL, :USL, :LSL, "
//                               ":STARTx, :STARTy, :STARTz, :STARTSCALE, :STARTLENS, :STARTINTENSITY_TOP, :STARTINTENSITY_BOTTOM, :STARTEXPOURETIME,"
//                               ":ENDx, :ENDy, :ENDz, :ENDSCALE, :ENDLENS, :ENDINTENSITY_TOP, :ENDINTENSITY_BOTTOM, :ENDEXPOURETIME);";
//        data->moveTo(i);
//        MeasurePair &pair = data->currentMeasure();
//        cmd.replace(":MeasureID", QString::number(i));
//        cmd.replace(":SIDE", QString::number(pair.side));
//        cmd.replace(":CL", QString::number(pair.CL));
//        cmd.replace(":UCL", QString::number(pair.UCL));
//        cmd.replace(":LCL", QString::number(pair.LCL));
//        cmd.replace(":USL", QString::number(pair.USL));
//        cmd.replace(":LSL", QString::number(pair.LSL));
//        cmd.replace(":STARTx", QString::number(pair.start.x_mm));
//        cmd.replace(":STARTy", QString::number(pair.start.y_mm));
//        cmd.replace(":STARTz", QString::number(pair.start.z_mm));
//        cmd.replace(":STARTSCALE", QString::number(pair.start.scale));
//        cmd.replace(":STARTLENS", QString::number(pair.start.lens));
//        cmd.replace(":STARTINTENSITY_TOP", QString::number(pair.start.topLampIntensity));
//        cmd.replace(":STARTINTENSITY_BOTTOM", QString::number(pair.start.bottomLampIntensity));
//        cmd.replace(":STARTEXPOURETIME", QString::number(pair.start.exposure));

//        cmd.replace(":ENDx", QString::number(pair.end.x_mm));
//        cmd.replace(":ENDy", QString::number(pair.end.y_mm));
//        cmd.replace(":ENDz", QString::number(pair.end.z_mm));
//        cmd.replace(":ENDSCALE", QString::number(pair.end.scale));
//        cmd.replace(":ENDLENS", QString::number(pair.end.lens));
//        cmd.replace(":ENDINTENSITY_TOP", QString::number(pair.end.topLampIntensity));
//        cmd.replace(":ENDINTENSITY_BOTTOM", QString::number(pair.end.bottomLampIntensity));
//        cmd.replace(":ENDEXPOURETIME", QString::number(pair.end.exposure));

//        qDebug() << cmd;
//        if(!query.exec(cmd))
//        {
//            qDebug() << "N = " << N;
//            return -7;
//        }
//    }
//    return 1;
//}
